package com.amado.easyui2.dao.impl;

import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import com.amado.easyui2.dao.DrivingSchoolDao;
import com.amado.easyui2.domain.DrivingSchoolEntity;
import com.amado.easyui2.util.DBConnection;
import com.amado.easyui2.vo.Page;

public class DrivingSchoolDaoImpl implements DrivingSchoolDao {

	@Override
	public void add(DrivingSchoolEntity cn) {
		Connection conn = JDBC.getConn();
		String sql = "insert into t_driving_school(driving_school_name,company_name,service_code,abbreviation,bh_name,bl_number,bh_first_solar,area,license_no,site,postal_code,business_scope,fn,legal_person,phone,register_serial_number,approval_unit,traffic_approval_number,bh_popedom,economic_nature,create_time,update_time,create_name,update_name)value(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			int i = 1;
			ps = conn.prepareStatement(sql);
			ps.setString(i++, cn.getDrivingSchoolName());
			ps.setString(i++, cn.getCompanyName());
//			ps.setDate(4, cn.getValidTime());
			ps.setString(i++, cn.getServiceCode());
			ps.setString(i++, cn.getAbbreviation());
			ps.setString(i++, cn.getBhName());
			ps.setString(i++, cn.getBlNumber());
//			ps.setDate(9, cn.getBlTalidTime());
			ps.setString(i++, cn.getBhFirstSolar());
			ps.setString(i++, cn.getArea());
			ps.setString(i++, cn.getLicenseNo());
			ps.setString(i++, cn.getSite());
			ps.setString(i++, cn.getPostalCode());
			ps.setString(i++, cn.getBusinessScope());
			ps.setString(i++, cn.getFn());
			ps.setString(i++, cn.getLegalPerson());
			ps.setString(i++, cn.getPhone());
			ps.setString(i++, cn.getRegisterSerialNumber());
			ps.setString(i++, cn.getApprovalUnit());
			ps.setString(i++, cn.getTrafficApprovalNumber());
			ps.setString(i++, cn.getBhPopedom());
			ps.setString(i++, cn.getEconomicNature());
			ps.setTimestamp(i++, new java.sql.Timestamp(System.currentTimeMillis()));
			ps.setTimestamp(i++, new java.sql.Timestamp(System.currentTimeMillis()));
			ps.setString(i++, cn.getCreateName());
			ps.setString(i++, cn.getUpdateName());
			ps.execute();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBC.close(null, ps, conn);

	}

//	@Override
//	public void delete(Long id) {
//		Connection conn = JDBC.getConn();
//		String sql = "update t_driving_school set delete_logo=? where id=?";
//		PreparedStatement ps = null;
//		try {
//			ps = conn.prepareStatement(sql);
//			ps.setString(1, "Y");
//			ps.setLong(2, id);
//			ps.executeQuery();
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		JDBC.close(null, ps, conn);
//	}

	@Override
	public int update(DrivingSchoolEntity id) {
		Connection conn = JDBC.getConn();
		String sql = "update t_driving_school set driving_school_name=?,company_name=?,service_code=?,abbreviation=?,bh_name=?,bl_number=?,bh_first_solar=?,area=?,license_no=?,site=?,postal_code=?,business_scope=?,fn=?,legal_person=?,phone=?,register_serial_number=? where id=?";
		PreparedStatement ps = null;
		try {
			int i=1;
			ps = conn.prepareStatement(sql);
			
			ps.setString(i++, id.getDrivingSchoolName());
			ps.setString(i++, id.getCompanyName());
//			ps.setDate(4, cn.getValidTime());
			ps.setString(i++, id.getServiceCode());
			ps.setString(i++, id.getAbbreviation());
			ps.setString(i++, id.getBhName());
			ps.setString(i++, id.getBlNumber());
//			ps.setDate(9, cn.getBlTalidTime());
			ps.setString(i++, id.getBhFirstSolar());
			ps.setString(i++, id.getArea());
			ps.setString(i++, id.getLicenseNo());
			ps.setString(i++, id.getSite());
			ps.setString(i++, id.getPostalCode());
			ps.setString(i++, id.getBusinessScope());
			ps.setString(i++, id.getFn());
			ps.setString(i++, id.getLegalPerson());
			ps.setString(i++, id.getPhone());
			ps.setString(i++, id.getRegisterSerialNumber());

			ps.setLong(i++, id.getId());
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBC.close(null, ps, conn);
		return 0;
	}

	@Override
	public List<DrivingSchoolEntity> listAll() {
		Connection conn = JDBC.getConn();
		String sql = "select * from t_driving_school";
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<DrivingSchoolEntity> driving = new ArrayList();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				int i = 1;
				Long id = rs.getLong(i++);
				String drivingSchoolName = rs.getString(i++);
				String companyName = rs.getString(i++);
				String validTime = rs.getString(i++);
				String serviceCode = rs.getString(i++);
				String abbreviation = rs.getString(i++);
				String bhName = rs.getString(i++);
				String blNumber = rs.getString(i++);
				Date blTalidTime = rs.getTimestamp(i++);
				String bhFirstSolar = rs.getString(i++);
				String area = rs.getString(i++);
				String licenseNo = rs.getString(i++);
				String site = rs.getString(i++);
				String postalCode = rs.getString(i++);
				String businessScope = rs.getString(i++);
				String fn = rs.getString(i++);
				String legalPerson = rs.getString(i++);
				String phone = rs.getString(i++);
				String registerSerialNumber = rs.getString(i++);
				String approvalUnit = rs.getString(i++);
				String trafficApprovalNumber = rs.getString(i++);
				Date approvalDay = rs.getTimestamp(i++);
				String bhPopedom = rs.getString(i++);
				String economicNature = rs.getString(i++);
				String deleteLogo=rs.getString(i++);
				Date createTime=rs.getTimestamp(i++);
				Date updateTime=rs.getTimestamp(i++);
				String createName=rs.getString(i++);
				String updateName=rs.getString(i++);
				driving.add(new DrivingSchoolEntity(id, drivingSchoolName, companyName, validTime, serviceCode,
						abbreviation, bhName, blNumber, blTalidTime, bhFirstSolar, area, licenseNo, site, postalCode,
						businessScope, fn, legalPerson, phone, registerSerialNumber,approvalUnit,trafficApprovalNumber,approvalDay,bhPopedom,economicNature,deleteLogo,
						createTime,updateTime,createName,updateName));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return driving;
	}

	@Override
	public List<DrivingSchoolEntity> login(DrivingSchoolEntity idCard) {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM t_driving_school where company_name =? and bl_number=?";
		List<DrivingSchoolEntity> list = new ArrayList<DrivingSchoolEntity>();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				int i = 1;
				String companyName = rs.getString(i++);
				String blNumber = rs.getString(i++);

				list.add(new DrivingSchoolEntity(companyName, blNumber));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<DrivingSchoolEntity> listAll(String cn, String bln) {
		Connection conn = JDBC.getConn();
		String sql = "select * from t_driving_school where delete_logo='N'";
		PreparedStatement ps = null;
		ResultSet rs = null;
		if (cn != null && cn.trim() != "") {
			sql = sql + " and company_name like ?";
		}
		if (bln != null && bln.trim() != "") {
			sql = sql + " and bl_number =?";
		}
		System.out.println("sql" + sql);
		List<DrivingSchoolEntity> driving = new ArrayList<DrivingSchoolEntity>();
		try {
			ps = conn.prepareStatement(sql);
			int j = 1;
			if (cn != null && cn.trim() != "") {
				ps.setString(j++, cn);
			}
			if (bln != null && bln.trim() != "") {
				ps.setString(j++, "%" + bln + "%");
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				int i = 1;
				Long id = rs.getLong(i++);
				String drivingSchoolName = rs.getString("driving_school_name");
				String companyName = rs.getString("company_name");
				String validTime = rs.getString("valid_time");
				String serviceCode = rs.getString("service_code");
				String abbreviation = rs.getString("abbreviation");
				String bhName = rs.getString("bh_name");
				String blNumber = rs.getString("bl_number");
				Date blValidTime = rs.getTimestamp("bl_valid_time");
				String bhFirstSolar = rs.getString("bh_first_solar");
				String area = rs.getString("area");
				String licenseNo = rs.getString("license_no");
				String site = rs.getString("site");
				String postalCode = rs.getString("postal_code");
				String businessScope = rs.getString("business_scope");
				String fn = rs.getString("fn");
				String legalPerson = rs.getString("legal_person");
				String phone = rs.getString("phone");
				String registerSerialNumber = rs.getString("register_serial_number");
				String approvalUnit = rs.getString("approval_unit");
				String trafficApprovalNumber = rs.getString("traffic_approval_number");
				Date approvalDay = rs.getTimestamp("approval_day");
				String bhPopedom = rs.getString("bh_popedom");
				String economicNature = rs.getString("economic_nature");
				String deleteLogo=rs.getString("delete_logo");
				Date createTime=rs.getTimestamp("create_time");
				Date updateTime=rs.getTimestamp("update_time");
				String createName=rs.getString("create_name");
				String updateName=rs.getString("update_name");
				driving.add(new DrivingSchoolEntity(id, drivingSchoolName, companyName, validTime, serviceCode,
						abbreviation, bhName, blNumber, blValidTime,bhFirstSolar,  area, licenseNo, site, postalCode,
						businessScope, fn, legalPerson, phone, registerSerialNumber,approvalUnit,trafficApprovalNumber,approvalDay,bhPopedom,economicNature,deleteLogo,
						createTime,updateTime,createName,updateName));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return driving;
	}

	@Override
	public int listAllCount(String cn, String bln) {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT count(1) FROM t_driving_school where delete_logo='N'";
		if (cn != null && cn.trim() != "") {
			sql = sql + " and company_name like ?";
		}
		if (bln != null && bln.trim() != "") {
			sql = sql + " and bl_number =?";
		}
		System.out.println("sql" + sql);
		List<DrivingSchoolEntity> list = new ArrayList<DrivingSchoolEntity>();
		try {
			ps = conn.prepareStatement(sql);
			int j = 1;
			if (cn != null && cn.trim() != "") {
				ps.setString(j++, cn);
			}
			if (bln != null && bln.trim() != "") {
				ps.setString(j++, "%" + bln + "%");
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps, conn);
		}
		return 0;
	}

	@Override
	public List<DrivingSchoolEntity> listAll(String cn, String bln, Page page) {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM t_driving_school where delete_logo='N'";
		if (cn != null && cn.trim() != "") {
			sql = sql + " and company_name =?";
		}
		if (bln != null && bln.trim() != "") {
			sql = sql + " and bl_number like ?";
		}
		sql = sql + "limit ?,?";
		System.out.println("sql" + sql);
		List<DrivingSchoolEntity> driving = new ArrayList<DrivingSchoolEntity>();
		try {
			ps = conn.prepareStatement(sql);
			int j = 1;
			if (cn != null && cn.trim() != "") {
				ps.setString(j++, cn);
			}
			if (bln != null && bln.trim() != "") {
				ps.setString(j++, "%" + bln + "%");
			}
			ps.setInt(j++, (page.getCurrentPage() - 1) * page.getRowSize());
			ps.setInt(j++, page.getRowSize());
			rs = ps.executeQuery();
			while (rs.next()) {
				int i = 1;
				Long id = rs.getLong(i++);
				String drivingSchoolName = rs.getString("driving_school_name");
				String companyName = rs.getString("company_name");
				String validTime = rs.getString("valid_time");
				String serviceCode = rs.getString("service_code");
				String abbreviation = rs.getString("abbreviation");
				String bhName = rs.getString("bh_name");
				String blNumber = rs.getString("bl_number");
				Date blValidTime = rs.getTimestamp("bl_valid_time");
				String bhFirstSolar = rs.getString("bh_first_solar");
				String area = rs.getString("area");
				String licenseNo = rs.getString("license_no");
				String site = rs.getString("site");
				String postalCode = rs.getString("postal_code");
				String businessScope = rs.getString("business_scope");
				String fn = rs.getString("fn");
				String legalPerson = rs.getString("legal_person");
				String phone = rs.getString("phone");
				String registerSerialNumber = rs.getString("register_serial_number");
				String approvalUnit = rs.getString("approval_unit");
				String trafficApprovalNumber = rs.getString("traffic_approval_number");
				Date approvalDay = rs.getTimestamp("approval_day");
				String bhPopedom = rs.getString("bh_popedom");
				String economicNature = rs.getString("economic_nature");
				String deleteLogo=rs.getString("delete_logo");
				Date createTime=rs.getTimestamp("create_time");
				Date updateTime=rs.getTimestamp("update_time");
				String createName=rs.getString("create_name");
				String updateName=rs.getString("update_name");
				driving.add(new DrivingSchoolEntity(id, drivingSchoolName, companyName, validTime, serviceCode,
						abbreviation, bhName, blNumber, blValidTime,bhFirstSolar,  area, licenseNo, site, postalCode,
						businessScope, fn, legalPerson, phone, registerSerialNumber,approvalUnit,trafficApprovalNumber,approvalDay,bhPopedom,economicNature,deleteLogo,
						createTime,updateTime,createName,updateName));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return driving;
	}

	@Override
	public void delete(List<Long> ids) {
		if(ids==null ||ids.isEmpty()) {
			return;
		}
		Connection conn = JDBC.getConn();
		// in (?)
		// in(?,?)
		StringBuilder sql = new StringBuilder("update t_driving_school set delete_logo=? where id in (");
		for(int i=0;i<ids.size();i++) {
			if(i==0) {
				sql.append("?");
			}else {
				sql.append(",?");
			}
		}
		sql.append(")");
		
		
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, "Y");
			for(int i=0;i<ids.size();i++) {
				ps.setLong(i+2, ids.get(i));
			}
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBC.close(null, ps, conn);
		
	}
	public static void main(String[] args) {
		new DrivingSchoolDaoImpl().delete(Arrays.asList(1L,2L,3L));
	}

}
